package co.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import co.com.util.ConexionDirecta;
import co.com.vo.ParticipanteVO;


public class ParticipanteDAO {
	
	private String SQL_INSERT = "INSERT INTO PARTICIPANTE (ID,NOMBRE) VALUES (?,?)";
	private String SQL_UPDATE = "UPDATE PARTICIPANTE SET NOMBRE=? WHERE ID=?";
	private String SQL_DELETE = "DELETE PARTICIPANTE WHERE ID=?";
	private String SQL_SELECT = "SELECT ID,NOMBRE FROM PARTICIPANTE";
	
	public boolean insertParticipante(ParticipanteVO participanteVO) throws SQLException{
		Connection conn =null;
		PreparedStatement pt=null;
		try {
			conn = ConexionDirecta.getOracleConnection();
			pt = conn.prepareStatement(SQL_INSERT);
			pt.setInt(1, participanteVO.getId());
			pt.setString(2, participanteVO.getNombre());
			
			pt.executeUpdate();
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				conn.close();
			}
			if(pt!=null){
				pt.close();
			}
		}
		
		return false;
	}
	
	public boolean updateParticipante(ParticipanteVO participanteVO) throws SQLException{
		
		Connection conn =null;
		PreparedStatement pt=null;
		try {
			conn = ConexionDirecta.getOracleConnection();
			pt = conn.prepareStatement(SQL_UPDATE);
			pt.setInt(2, participanteVO.getId());
			pt.setString(1, participanteVO.getNombre());
			
			pt.executeUpdate();
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				conn.close();
			}
			if(pt!=null){
				pt.close();
			}
		}
		
		return false;
		
	}
	
	public boolean deleteParticipante(ParticipanteVO participanteVO) throws SQLException{
		Connection conn =null;
		PreparedStatement pt=null;
		try {
			conn = ConexionDirecta.getOracleConnection();
			pt = conn.prepareStatement(SQL_DELETE);
			pt.setInt(1, participanteVO.getId());
			
			
			pt.executeUpdate();
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				conn.close();
			}
			if(pt!=null){
				pt.close();
			}
		}
		
		return false;
	}
	
	public List<ParticipanteVO> selectParticipante(ParticipanteVO participanteVO) throws SQLException{
		Connection conn =null;
		PreparedStatement pt=null;
		ResultSet rs = null;
		List<ParticipanteVO> lista = new ArrayList<ParticipanteVO>();
		try {
			
			if(participanteVO == null){
				return null;
			}
			
			StringBuffer st = new StringBuffer();
			st.append(SQL_SELECT+" WHERE ");
			
			boolean flag=false;
			
			if(participanteVO.getId()!=null){
				st.append("ID="+participanteVO.getId());
				flag=true;
			}
			
			if(participanteVO.getNombre()!=null && !participanteVO.getNombre().equals("")){
				if(flag){
					st.append(",");
				}
				st.append("NOMBRE="+participanteVO.getNombre());
				flag=true;
			}
			
			conn = ConexionDirecta.getOracleConnection();
			pt = conn.prepareStatement(st.toString());
			
			rs=pt.executeQuery();
			
			while(rs.next()){
				ParticipanteVO aux=new ParticipanteVO();
				aux.setId(rs.getInt(1));
				aux.setNombre(rs.getString(2));
				lista.add(aux);
			}
			
			return lista;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				conn.close();
			}
			if(pt!=null){
				pt.close();
			}
			if(rs!=null){
				rs.close();
			}
		}
		
		return null;	
	}
	
	public List<ParticipanteVO> selectAllParticipante() throws SQLException{
		Connection conn =null;
		PreparedStatement pt=null;
		ResultSet rs = null;
		List<ParticipanteVO> lista = new ArrayList<ParticipanteVO>();
		try {
			conn = ConexionDirecta.getOracleConnection();
			pt = conn.prepareStatement(SQL_SELECT);
			
			rs=pt.executeQuery();
			
			while(rs.next()){
				ParticipanteVO aux=new ParticipanteVO();
				aux.setId(rs.getInt(1));
				aux.setNombre(rs.getString(2));
				lista.add(aux);
			}
			
			return lista;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				conn.close();
			}
			if(pt!=null){
				pt.close();
			}
			if(rs!=null){
				rs.close();
			}
		}
		
		return null;
	}

}
